import os,shutil,glob,sys
from src.db_utils import *
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta, TH, WE
from docx import Document
from docx.oxml import OxmlElement
from docx.oxml.ns import qn
from docx.table import Table
from docx.text.paragraph import Paragraph
from docx.shared import Pt,RGBColor
from PyQt5.QtWidgets import QWidget,QFileDialog,QApplication
from PyQt5.QtCore import QDateTime,Qt
from qfluentwidgets import InfoBarPosition, InfoBar
from ui.export_weekly_ui import Ui_export_weekly
import win32com.client as win32
from win32com.client import constants
from pdf2image import convert_from_path
from PIL import Image
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Inches


class export_weekly_cls(Ui_export_weekly,QWidget):

    def __init__(self, parent=None):
        super().__init__(parent=parent)
        self.setupUi(self)
        self.initialize_time()
        self.day_report.clicked.connect(self.export_day_txt)
        self.day_report_2.clicked.connect(self.export_one_day_txt)
        self.week_report.clicked.connect(self.export_week)
        # self.mergeDocuments.clicked.connect(self.mergeDocuments_def)
    
    def export_day_txt(self):
        self.export_report(current_date=datetime.now())

    def export_one_day_txt(self):
        current_date = self.day_date.dateTime().toPyDateTime()
        self.export_report(current_date=current_date)

    def export_report(self, current_date):
        db = Database()
        c = db.get_connection()
        this_time = current_date.strftime('%Y%m%d')
        start_date = current_date.replace(hour=0, minute=0, second=0, microsecond=0)
        end_date = start_date.replace(hour=23, minute=59, second=59)

        query_local_work = """
            SELECT (CASE WHEN (custom_department <> '' AND custom_department IS NOT NULL) THEN custom_department ELSE custom_company END) AS name,
                custom_name, serial_num, question_ask || ':' || question_done || '---' ||
                (CASE WHEN (column_status LIKE '%处理中%' OR column_status LIKE '%待受理%' OR column_status LIKE '%待上门%') THEN '任务持续中' ELSE '任务已完成' END) AS content
            FROM ticket 
            WHERE (start_time BETWEEN :start_date AND :end_date OR local_end_time BETWEEN :start_date AND :end_date) 
            AND (is_del <> '1' OR is_del IS NULL)
        """

        c.execute(query_local_work, {"start_date": start_date, "end_date": end_date})
        rows = c.fetchall()
        c.close()

        # 格式化输出
        print(f"{this_time}工作日报：")
        self.day_text.clear()
        self.day_text.append(f"{this_time}工作日报：")
        for idx, row in enumerate(rows, 1):
            name = row[0]
            custom_name = row[1]
            serial_num = row[2]
            content = row[3]
            print(f"{idx}、{name}--{custom_name}/{serial_num}--{content}")
            self.day_text.append(f"{idx}、{name}--{custom_name}/{serial_num}--{content}")

        clipboard = QApplication.clipboard()
        clipboard.setText(self.day_text.toPlainText())
        InfoBar.info(title='信息', content="日报已复制", orient=Qt.Horizontal, isClosable=True, position=InfoBarPosition.TOP_RIGHT, duration=20000, parent=self)

    def initialize_time(self):
        start_date_str = (datetime.today() + relativedelta(weekday=TH(-1))).strftime('%Y-%m-%d')
        start_date = QDateTime.fromString(start_date_str, 'yyyy-MM-dd')
        self.start_date.setDateTime(start_date)
        yesterday = datetime.today() - timedelta(days=1)
        yesterday_str = yesterday.strftime('%Y-%m-%d')
        yesterday_date = QDateTime.fromString(yesterday_str, 'yyyy-MM-dd')
        self.day_date.setDateTime(yesterday_date)
        end_date_str = (datetime.today() + relativedelta(weekday=WE)).strftime('%Y-%m-%d')
        end_date = QDateTime.fromString(end_date_str, 'yyyy-MM-dd')
        self.end_date.setDateTime(end_date)                

    def delete_random_folder(self, base_path):
        # 查找所有文件夹路径
        folder_paths = glob.glob(os.path.join(base_path, '*'))
        # 遍历所有文件夹路径
        for folder_path in folder_paths:
            if os.path.isdir(folder_path):
                try:
                    shutil.rmtree(folder_path)
                    print(f"Deleted folder: {folder_path}")
                except Exception as e:
                    print(f"Error deleting {folder_path}: {e}")

    def export_week(self):
        db = Database()
        c = db.get_connection()
        current_directory = db.load_folder_path()
        doc_path = os.path.join(current_directory, 'res', 'docx', '周报模板.docx')
        doc = Document(doc_path)
        current_time = datetime.now()
        this_time = str(datetime.now().strftime('%Y%m%d'))
        start_date = self.start_date.dateTime().toPyDateTime()
        end_date = self.end_date.dateTime().toPyDateTime()
        end_date = end_date.replace(day=end_date.day, hour=23, minute=59, second=59)

        current_year = str(current_time.year)
        current_month = str(current_time.month)
        current_day = str(current_time.day)

        default_area = db.get_default_set_data('default_area')
        default_name = db.get_default_set_data('default_name')

        # 1.处理表头
        replace_dict = {
            '{{default_area}}': default_area,
            '{{year}}': current_year,
            '{{month}}': current_month,
            '{{day}}': current_day,
            '{{default_name}}': default_name
        }

        for para in doc.paragraphs:
            # original_text = para.text
            # print(f"Original Paragraph: {original_text}")
            # 将整个段落的文本合并为一个字符串
            combined_text = ''.join([run.text for run in para.runs])
            # 替换占位符
            updated_text = combined_text
            for placeholder, value in replace_dict.items():
                updated_text = updated_text.replace(placeholder, value)
            # 输出更新后的合并文本
            # print(f"Updated Combined Text: {updated_text}")
            # 使用 updated_text 来更新每个 run
            run_start = 0  # 记录当前位置
            for run in para.runs:
                run_end = run_start + len(run.text)  # 获取运行文本的结束位置
                run_start = run_end
                
                # 获取替换后的文本片段，并更新当前 run
                new_run_text = updated_text[run_start - len(run.text):run_end]
                run.text = new_run_text
                # print(f"Replaced in run: {run.text}")

        # 2.处理第二个的表格        
        query_l1_counts = """
            SELECT 
                CASE 
                    WHEN column_status LIKE 'L1处理中%' OR column_status LIKE 'L1待上门%' THEN 'L1合计'
                    WHEN column_status LIKE 'L2处理中%' OR column_status LIKE 'L2待受理%' THEN 'L2合计'
                    WHEN column_status LIKE 'L3处理中%' OR column_status LIKE 'L3待受理%' THEN 'L3合计'
                    WHEN column_status LIKE '待验证%' THEN 'L1待验证'
                    WHEN column_status LIKE '已挂起%' THEN 'L1已挂起'
                    ELSE column_status
                END AS status_group,
                COUNT(*) 
            FROM ticket 
            WHERE (is_del <> '1' OR is_del is NULL) 
            GROUP BY status_group
        """
        c.execute(query_l1_counts)
        counts = c.fetchall()
        count_dict = {row[0]: row[1] for row in counts}

        l1_count = str(count_dict.get('L1合计', 0))
        l2_count = str(count_dict.get('L2合计', 0))
        l3_count = str(count_dict.get('L3合计', 0))
        l1_verify = str(count_dict.get('L1待验证', 0))
        l1_hung = str(count_dict.get('L1已挂起', 0))

        replace_table_dict = {
            '{{default_name}}': default_name,
            '{{l1_count}}': l1_count,
            '{{l2_count}}': l2_count,
            '{{l3_count}}': l3_count,
            '{{l1_verify}}': l1_verify,
            '{{l1_hung}}': l1_hung
        }

        for table in doc.tables:
            for row in table.rows:
                for cell in row.cells:
                    for placeholder, value in replace_table_dict.items():
                        if placeholder in cell.text:
                            cell.text = cell.text.replace(placeholder, value)
                            for paragraph in cell.paragraphs:
                                for run in paragraph.runs:
                                    if default_name in run.text:
                                        run.font.size = Pt(10)
                                        rPr = run._r.get_or_add_rPr()  # 获取字体样式
                                        rFonts = OxmlElement('w:rFonts')  # 创建字体元素
                                        rFonts.set(qn('w:eastAsia'), '微软雅黑')  # 设置东亚字体为“微软雅黑”
                                        rPr.append(rFonts)  # 添加到文本的字体样式中
                                    else:
                                        run.font.name = '微软雅黑'
                                        run.font.size = Pt(10)

        # 下周计划
        query_next_week = """
        SELECT ROW_NUMBER( ) OVER ( ORDER BY start_time ) || '、' || 
            ( CASE WHEN ( custom_department <> '' AND custom_department IS NOT NULL ) THEN custom_department ELSE custom_company END ) ||
            '：' || question_ask  as nextweek
        FROM ticket WHERE (column_status LIKE '%处理中%' OR column_status LIKE '%待受理%' OR column_status LIKE '%待上门%') AND (is_del <> '1' OR is_del is NULL) ORDER BY start_time ASC
        """
        c.execute(query_next_week)
        work_orders = c.fetchall()
        for idx, paragraph in enumerate(doc.paragraphs):
            if "next_week" in paragraph.text:
                bookmark_index = idx
                paragraph.clear()  # 清空该段落的文本
                break
        if bookmark_index is not None:
            for index, work_order in enumerate(reversed(work_orders), start=1):
                # 在找到的位置插入新段落，并保留格式
                new_paragraph = doc.paragraphs[bookmark_index].insert_paragraph_before()
                # new_paragraph.add_run(f"{work_order[0]}")

                # 设置段落格式
                new_paragraph.alignment = WD_ALIGN_PARAGRAPH.LEFT  # 设置对齐方式为左对齐
                new_paragraph.paragraph_format.line_spacing = 1.5  # 设置行间距为1.5倍行距
                new_paragraph.paragraph_format.left_indent = Inches(0.228)
                # new_paragraph.paragraph_format.space_before = Pt(12)  # 设置段落前间距为12磅
                # new_paragraph.paragraph_format.space_after = Pt(12)  # 设置段落后间距为12磅

                # 设置字体样式
                run = new_paragraph.add_run(f"{work_order[0]}")  # 插入文本

                # 设置字符格式（如字体、字号、颜色等）
                # run.font.name = '微软雅黑'  # 设置字体为Arial
                run.font.size = Pt(12)  # 设置字号为12磅
                # run.font.bold = True  # 设置为加粗
                # run.font.color.rgb = RGBColor(0, 0, 255)  # 设置字体颜色为蓝色
                rPr = run._r.get_or_add_rPr()  # 获取字体样式
                rFonts = OxmlElement('w:rFonts')  # 创建字体元素
                rFonts.set(qn('w:eastAsia'), '微软雅黑')  # 设置东亚字体为“微软雅黑”
                rPr.append(rFonts)  # 添加到文本的字体样式中
        else:
            print("未找到标记。")

        # 表格处理
        def process_table(query, table_index, start_date=None, end_date=None):
            if start_date and end_date:
                c.execute(query, {"start_date": start_date, "end_date": end_date})
            else:
                c.execute(query)
            work_orders = c.fetchall()

            # for table in doc.tables:
            #     for row_idx, row in enumerate(table.rows):
            #         for cell_idx, cell in enumerate(row.cells):
            #             bookmark_index = (row_idx, cell_idx)
            #             break
            # row_index, column_index = bookmark_index
            table = doc.tables[table_index]
            for data_row in work_orders:
                new_row = table.add_row().cells
                for idx, value in enumerate(data_row):
                    new_row[idx].text = str(value)

        # 1.本周重点
        query_import_work = """
            SELECT (CASE WHEN (custom_department <> '' AND custom_department IS NOT NULL) THEN custom_department ELSE  custom_company  END) AS department,
                ticket_phase,(question_ask || '。' || question_done ) AS content,'无'  AS fengxian,
                (CASE WHEN (column_status LIKE '%处理中%' OR column_status LIKE '%待受理%' OR column_status LIKE '%待上门%') THEN '持续中' ELSE '已完成' END) workdone,
                (SELECT default_name FROM DEFAULT_SET) AS default_name
                FROM ticket WHERE 
                ((start_time IS NOT NULL AND start_time BETWEEN :start_date AND :end_date) OR 
                (local_end_time IS NOT NULL AND local_end_time BETWEEN :start_date AND :end_date))
                AND is_import = '1' 
                AND (is_del <> '1' OR is_del is NULL)
        """
        process_table(query_import_work, 0, start_date, end_date)
        
        # 2.遗留问题处理
        query_yiliu_work = """
            SELECT (SELECT default_name FROM DEFAULT_SET ) AS default_name, SUBSTR( service_level, 1, INSTR ( service_level, ' ' ) - 1 ) AS level,
            ( CASE WHEN ( custom_department <> '' AND custom_department IS NOT NULL ) THEN custom_department ELSE custom_company END ) AS department,
            question_ask, question_done
        FROM ticket WHERE (column_status LIKE '%L2%' OR column_status LIKE '%L3%') AND (is_del <> '1' OR is_del is NULL)  ORDER BY start_time ASC
        """
        process_table(query_yiliu_work, 2)

        # 3.本地表格处理
        query_local_work = """
            SELECT (SELECT default_name FROM DEFAULT_SET) AS default_name ,
                (CASE WHEN (custom_department <> '' AND custom_department IS NOT NULL) THEN custom_department ELSE  custom_company  END) AS department,
                question_ask || CHAR(10) ||
                replace(
                    question_done,
                    SUBSTR(question_done, 1, 2), 
                    CHAR(10) || SUBSTR(question_done, 1, 2)
                ) AS content,
                (CASE WHEN (column_status LIKE '%处理中%' OR column_status LIKE '%待受理%' OR column_status LIKE '%待上门%') THEN '任务持续中' ELSE '任务已完成' END) AS workdone
                FROM ticket WHERE 
                ((start_time IS NOT NULL AND start_time BETWEEN :start_date AND :end_date) OR 
                (local_end_time IS NOT NULL AND local_end_time BETWEEN :start_date AND :end_date))
                AND (is_del <> '1' OR is_del is NULL)
        """
        process_table(query_local_work, 3, start_date, end_date)

        # 4.日常任务
        query_normal_work = """
            SELECT 
                (SELECT default_name FROM DEFAULT_SET) AS default_name,is_worktime,
                replace(
                    work_contect,
                    SUBSTR(work_contect, 1, 2), 
                    CHAR(10) || SUBSTR(work_contect, 1, 2)
                ) AS content
            FROM ticket_time
            WHERE (start_time BETWEEN :start_date AND :end_date OR end_time BETWEEN :start_date AND :end_date)
                AND (is_worktime  <> '工单' AND is_worktime  <> '项目') AND (is_del <> '1' OR is_del is NULL)
        """
        process_table(query_normal_work, 4, start_date, end_date)

        
        # 表格颜色处理
        def process_cell_text(cell, target_text, color):
            cell_text = cell.text
            if target_text in cell_text:
                index = cell_text.find(target_text)
                before_text = cell_text[:index]
                after_text = cell_text[index:]
                for p in cell.paragraphs:
                    cell._element.remove(p._element)
                new_p = cell.add_paragraph(before_text)
                new_run = new_p.add_run(after_text)
                new_run.font.color.rgb = color

        for table in doc.tables:
            for row in table.rows:
                for cell in row.cells:
                    process_cell_text(cell, "任务持续中", RGBColor(0x00, 0x70, 0xC0))
                    process_cell_text(cell, "任务已完成", RGBColor(0x00, 0xB0, 0x50))
                    process_cell_text(cell, "任务挂起中", RGBColor(0xFF, 0xC0, 0x00))
                    process_cell_text(cell, "任务未完成", RGBColor(0xFF, 0x00, 0x00))



        # 保存修改后的文档
        filename = f'{current_directory}/西南售后服务部-周报-{this_time}-{default_name}.docx'
        doc.save(filename)
        
        def open_folder(folder_path):
            if sys.platform.startswith('win'):
                folder_path = folder_path.replace("/", "\\")
            os.system(f'explorer "{folder_path}"')

        open_folder(current_directory)
